package com.xiaolin.admin.utils.excel;

import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.xiaolin.admin.annotations.ExcelField;
import com.xiaolin.admin.exception.ServiceException;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URL;
import java.net.URLConnection;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * @author xiaolin
 * @description 表格工具类
 * @since 2024/8/28
 */
public class ExcelImportUtil {

    private static final Logger log = LoggerFactory.getLogger(ExcelImportUtil.class);

    /**
     * @Description: 动态解析并收集一级表头类型的Excel数据（Bean版）
     * @Author: xiaolin
     * @Date: 2022/8/18
     */
    public static <T> List<T> collectDataForPrimaryHeader(String filePath, Class<T> clazz) throws Exception {
        // 默认表头行是第二行
        return dynamicCollectData(1, 1, filePath, clazz);
    }

    /**
     * @Description: 动态解析并收集一级表头类型的Excel数据（Bean版）
     * @Author: xiaolin
     * @Date: 2022/8/18
     */
    public static <T> List<T> collectDataForPrimaryHeader(String filePath, Class<T> clazz, int headerIndex) throws Exception {
        // 默认表头行是第二行
        return dynamicCollectData(1, headerIndex, filePath, clazz);
    }

    /**
     * @Description: 动态解析并收集二级表头类型的Excel数据（Bean版）
     * @Author: xiaolin
     * @Date: 2022/8/10
     */
    public static <T> List<T> collectDataForSecondaryHeader(String filePath, Class<T> clazz) throws Exception {
        // 默认表头行是第二行
        return dynamicCollectData(2, 1, filePath, clazz);
    }

    /**
     * @Description: 动态解析并导出二级表头类型的Excel数据（Bean版）
     * @Author: xiaolin
     * @Date: 2022/8/10
     */
    public static <T> void exportDataForSecondaryHeader(String filePath, List<T> list, Class<T> clazz, int headerIndex, HttpServletResponse response) {
        // 默认表头行是第二行
        exportCollectData(2, headerIndex, filePath, list, clazz, response);
    }

    public static <T> void exportDataForPrimaryHeader(String filePath, List<T> list, Class<T> clazz, int headerIndex, HttpServletResponse response) {
        // 默认表头行是第一行
        exportCollectDataBigDecimal(1, headerIndex, filePath, list, clazz, response);
    }

    public static <T> ByteArrayOutputStream uploadFilesDataForPrimaryHeader(String filePath, List<T> list, Class<T> clazz, int headerIndex) {
        // 默认表头行是第一行
        return uploadFilesCollectData(1, headerIndex, filePath, list, clazz);
    }

    /**
     * @Description: 动态解析并收集Excel数据（Bean版）
     * @Author: xiaolin
     * @Date: 2022/8/10
     */
    private static <T> List<T> dynamicCollectData(Integer headerCount, Integer headerIndex, String filePath, Class<T> clazz) throws Exception {
        // 获取Iterator<Sheet>对象
        Iterator<Sheet> sheetIterator = getSheetIterator(filePath);
        // 记录
        List<T> records = new ArrayList<>();
        // 只解析第一个工作表
        Sheet sheet = sheetIterator.next();
        //校验表头并获取属性名及对应的列
        JSONObject relation = null;
        if (headerCount == 1) {
            relation = checkPrimaryHeaderAndGetRelation(sheet, clazz, headerIndex);
        } else {
            relation = checkSecondaryHeaderAndGetRelation(sheet, clazz, headerIndex);
        }
        // 表头索引
        boolean isMatch = relation.getBooleanValue("isMatch");
        if (!isMatch) {
            // 抛出异常
            String errorMsg = relation.getString("errorMsg");
            throw new ServiceException("表头解析出现错误：" + errorMsg);
        }
        int endIndex = sheet.getLastRowNum();
        Map<String, Integer> fieldRelations = (Map<String, Integer>) relation.get("relations");
        for (int rowNum = headerIndex + headerCount; rowNum <= endIndex; rowNum++) {
            Row row = sheet.getRow(rowNum);
            // 空行校验
            if (isEmptyRow(row)) continue;
            T record = clazz.newInstance();
            for (String fieldName : fieldRelations.keySet()) {
                // 获取 ExcelField 注解
                int colNum = fieldRelations.get(fieldName);
                // 获取单元格
                Cell cell = row.getCell(colNum);
                String setter = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                Field field = clazz.getDeclaredField(fieldName);
                Method method = clazz.getMethod(setter, field.getType());

                ExcelField excelField = field.getAnnotation(ExcelField.class);
                String title = excelField.value();

                if (Objects.nonNull(cell)) {
                    try {
                        // 获取属性对应的setter方法
                        setValue(cell, method, field, record);
                    } catch (NumberFormatException e) {
                        e.printStackTrace();
                        log.error("第{}行的[{}]列格式有问题，请检查", rowNum + 1, title);
                        throw new ServiceException("第" + (rowNum + 1) + "行的[" + title + "]列格式有问题，请检查");
                    } catch (ServiceException e) {
                        e.printStackTrace();
                        log.error("第{}行的[{}]列格式有问题，请检查", rowNum + 1, title);
                        throw new ServiceException(e.getMessage());
                    } catch (Exception e) {
                        e.printStackTrace();
                        log.error("第{}行的[{}]列格式有问题，请检查", rowNum + 1, title);
                        throw new ServiceException("第" + (rowNum + 1) + "行的[" + title + "]列格式有问题，请检查");
                    }
                } else {
                    setNullProperties(method, field, record);
                }
            }
            records.add(record);
        }
        return records;
    }

    /**
     * @Description: 动态解析并导出Excel数据（Bean版）
     * @Author: xiaolin
     * @Date: 2023/3/6
     */
    private static <T> void exportCollectData(Integer headerCount, Integer headerIndex, String filePath, List<T> list, Class<T> clazz, HttpServletResponse response) {
        try (InputStream inputStream = ExcelImportUtil.class.getClassLoader().getResourceAsStream(filePath);
             Workbook excel = filePath.lastIndexOf("xlsx") != -1 ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);
             OutputStream out = response.getOutputStream()) {
            //  获取第一个表格
            Sheet sheet = excel.getSheetAt(0);
            //校验表头并获取属性名及对应的列
            JSONObject relation = null;
            if (headerCount == 1) {
                relation = checkPrimaryHeaderAndGetRelation(sheet, clazz, headerIndex);
            } else {
                relation = checkSecondaryHeaderAndGetRelation(sheet, clazz, headerIndex);
            }
            // 表头索引
            boolean isMatch = relation.getBooleanValue("isMatch");
            if (!isMatch) {
                // 抛出异常
                String errorMsg = relation.getString("errorMsg");
                throw new ServiceException("表头解析出现错误：" + errorMsg);
            }
            Map<String, Integer> fieldRelations = (Map<String, Integer>) relation.get("relations");
            int rowIndex = headerCount + headerIndex;
            for (T t : list) {
                Class<T> beanClass = (Class<T>) t.getClass();
                Row row = sheet.createRow(rowIndex++);
                for (String fieldName : fieldRelations.keySet()) {
                    // 获取 ExcelField 注解
                    int colNum = fieldRelations.get(fieldName);
                    // 获取单元格
                    Cell cell = row.createCell(colNum);
                    String getter = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Method method = beanClass.getMethod(getter);
                    final Object data = method.invoke(t);
                    String fieldValue = "";
                    if (Objects.nonNull(data)) {
                        fieldValue = String.valueOf(method.invoke(t));
                    }
                    cell.setCellValue(fieldValue);
                }
            }
            response.setContentType("application/octet-stream");
            excel.write(out);
        } catch (ServiceException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }


    private static <T> ByteArrayOutputStream uploadFilesCollectData(Integer headerCount, Integer headerIndex, String filePath, List<T> list, Class<T> clazz) {
        try (InputStream inputStream = ExcelImportUtil.class.getClassLoader().getResourceAsStream(filePath);
             Workbook excel = filePath.lastIndexOf("xlsx") != -1 ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);
        ) {
            //  获取第一个表格
            Sheet sheet = excel.getSheetAt(0);
            //校验表头并获取属性名及对应的列
            JSONObject relation = null;
            if (headerCount == 1) {
                relation = checkPrimaryHeaderAndGetRelation(sheet, clazz, headerIndex);
            } else {
                relation = checkSecondaryHeaderAndGetRelation(sheet, clazz, headerIndex);
            }
            // 表头索引
            boolean isMatch = relation.getBooleanValue("isMatch");
            if (!isMatch) {
                // 抛出异常
                String errorMsg = relation.getString("errorMsg");
                throw new ServiceException("表头解析出现错误：" + errorMsg);
            }
            Map<String, Integer> fieldRelations = (Map<String, Integer>) relation.get("relations");
            int rowIndex = headerCount + headerIndex;
            for (T t : list) {
                Class<T> beanClass = (Class<T>) t.getClass();
                Row row = sheet.createRow(rowIndex++);
                for (String fieldName : fieldRelations.keySet()) {
                    // 获取 ExcelField 注解
                    int colNum = fieldRelations.get(fieldName);
                    // 获取单元格
                    Cell cell = row.createCell(colNum);
                    row.setHeightInPoints(row.getHeightInPoints());
                    String getter = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Method method = beanClass.getMethod(getter);
                    Class<?> type = beanClass.getDeclaredField(fieldName).getType();
                    final Object data = method.invoke(t);
                    if (type == BigDecimal.class) {
                        if (data != null) {
                            cell.setCellValue(((BigDecimal) data).doubleValue());
                        }
                    } else {
                        String fieldValue = "";
                        if (Objects.nonNull(data)) {
                            fieldValue = String.valueOf(method.invoke(t));
                        }
                        cell.setCellValue(fieldValue);
                    }
                }
            }
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            excel.write(bos);
            return bos;
        } catch (ServiceException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }

    private static <T> void exportCollectDataBigDecimal(Integer headerCount, Integer headerIndex, String filePath, List<T> list, Class<T> clazz, HttpServletResponse response) {
        try (InputStream inputStream = ExcelImportUtil.class.getClassLoader().getResourceAsStream(filePath);
             Workbook excel = filePath.lastIndexOf("xlsx") != -1 ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);
             OutputStream out = response.getOutputStream()) {
            //  获取第一个表格
            Sheet sheet = excel.getSheetAt(0);
            //校验表头并获取属性名及对应的列
            JSONObject relation = null;
            if (headerCount == 1) {
                relation = checkPrimaryHeaderAndGetRelation(sheet, clazz, headerIndex);
            } else {
                relation = checkSecondaryHeaderAndGetRelation(sheet, clazz, headerIndex);
            }
            // 表头索引
            boolean isMatch = relation.getBooleanValue("isMatch");
            if (!isMatch) {
                // 抛出异常
                String errorMsg = relation.getString("errorMsg");
                throw new ServiceException("表头解析出现错误：" + errorMsg);
            }
            Map<String, Integer> fieldRelations = (Map<String, Integer>) relation.get("relations");
            int rowIndex = headerCount + headerIndex;
            for (T t : list) {
                Class<T> beanClass = (Class<T>) t.getClass();
                Row row = sheet.createRow(rowIndex++);
                for (String fieldName : fieldRelations.keySet()) {
                    // 获取 ExcelField 注解
                    int colNum = fieldRelations.get(fieldName);
                    // 获取单元格
                    Cell cell = row.createCell(colNum);
                    row.setHeightInPoints(row.getHeightInPoints());
                    String getter = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Method method = beanClass.getMethod(getter);
                    Class<?> type = beanClass.getDeclaredField(fieldName).getType();
                    final Object data = method.invoke(t);
                    if (type == BigDecimal.class) {
                        if (data != null) {
                            cell.setCellValue(((BigDecimal) data).doubleValue());
                        }
                    } else {
                        String fieldValue = "";
                        if (Objects.nonNull(data)) {
                            fieldValue = String.valueOf(method.invoke(t));
                        }
                        cell.setCellValue(fieldValue);
                    }
                }
            }
            response.setContentType("application/octet-stream");
            excel.write(out);
        } catch (ServiceException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * @Description: 静态解析并收集Excel数据（Bean版）
     * @Author: xiaolin
     * @Date: 2022/6/23
     */
    public static <T> List<T> collectData(String filePath, Class<T> clazz) throws Exception {
        // 获取Iterator<Sheet>对象
        Iterator<Sheet> sheetIterator = getSheetIterator(filePath);
        // 记录
        List<T> records = new ArrayList<>();
        while (sheetIterator.hasNext()) {
            Sheet sheet = sheetIterator.next();
            //属性对应的列号和中文名
            String[][] fieldMarks = getFieldMarks(clazz);
            // 表头索引
            int headerIndex = getHeaderIndex(sheet, fieldMarks);
            if (headerIndex != -1) {
                // 只操作符合格式的页签
                int endIndex = sheet.getLastRowNum();
                for (int rowNum = headerIndex + 1; rowNum <= endIndex; rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (isEmptyRow(row)) continue;
                    T record = clazz.newInstance();
                    for (Field field : clazz.getDeclaredFields()) {
                        // 获取 ExcelField 注解
                        ExcelField excelField = field.getAnnotation(ExcelField.class);
                        int colNum = excelField.colNum();
                        // 获取单元格
                        Cell cell = row.getCell(colNum);
                        if (Objects.nonNull(cell)) {
                            // 设置属性值
                            String fieldName = field.getName();
                            // 获取属性对应的setter方法
                            String setter = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                            Method method = clazz.getMethod(setter, field.getType());
                            setValue(cell, method, field, record);
                        }
                    }
                    records.add(record);
                }
            }
        }
        return records;
    }

    /**
     * @Description:静态解析并收集Excel数据（Map版）
     * @Author: xiaolin
     * @Date: 2022/6/23
     */
    public static List<Map<String, String>> collectData(String filePath, Map<String, String> params) throws IOException {
        // 获取Iterator<Sheet>对象
        Iterator<Sheet> sheetIterator = getSheetIterator(filePath);
        List<Map<String, String>> records = new ArrayList<>();
        while (sheetIterator.hasNext()) {
            // 获取页签
            Sheet sheet = sheetIterator.next();
            // 获取表头关联关系
            JSONObject headerObj = getHeaderObj(sheet, params);
            // 表头索引
            int headerIndex = headerObj.getInteger("index");
            if (headerIndex != -1) {
                // 只操作符合格式的页签
                int endIndex = sheet.getLastRowNum();
                List<String> titles = headerObj.getObject("titles", new TypeReference<List<String>>() {
                });
                for (int rowNum = headerIndex + 1; rowNum <= endIndex; rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (isEmptyRow(row)) continue;
                    Map<String, String> record = new HashMap<>();
                    int colIndex = 0;
                    for (int i = 0; i < params.size(); i++) {
                        Cell cell = row.getCell(i);
                        if (cell == null) {
                            record.put(titles.get(colIndex), "");
                        } else {
                            switch (cell.getCellTypeEnum()) {
                                case BLANK:
                                    // 空白单元格
                                    record.put(titles.get(colIndex), "");
                                    break;
                                case STRING:
                                    // 字符串类型
                                    record.put(titles.get(colIndex), cell.getStringCellValue());
                                    break;
                                case NUMERIC:
                                    if (HSSFDateUtil.isCellInternalDateFormatted(cell)) {
                                        // 日期类型的格式
                                        Date date = cell.getDateCellValue();
                                        if (date != null) {
                                            // 将Excel的日期转成字符串格式
                                            String dateStr = convertExcelDateToString(date, cell);
                                            record.put(titles.get(colIndex), dateStr);
                                        } else {
                                            record.put(titles.get(colIndex), "");
                                        }
                                    } else {
                                        // 数字类型的格式
                                        String numberFormat = new HSSFDataFormatter().formatCellValue(cell);
                                        if (StringUtils.isNotEmpty(numberFormat)) {
                                            // 将Excel的数字转成字符串格式
                                            String numberStr = convertExcelNumberToString(numberFormat, cell);
                                            record.put(titles.get(colIndex), numberStr);
                                        } else {
                                            record.put(titles.get(colIndex), "");
                                        }
                                    }
                                    break;
                                case BOOLEAN:
                                    record.put(titles.get(colIndex), Boolean.toString(cell.getBooleanCellValue()));
                                    break;
                                case FORMULA:
                                    record.put(titles.get(colIndex), cell.getCellFormula());
                                    break;
                                default:
                                    record.put(titles.get(colIndex), cell.getStringCellValue());
                                    break;
                            }
                        }
                        colIndex++;
                    }
                    records.add(record);
                }
            }
        }
        return records;
    }

    /**
     * @Description: 检查空行
     * @Author: xiaolin
     * @Date: 2022/6/13
     */
    public static boolean isEmptyRow(Row row) {
        if (null == row) {
            return true;
        }
        //第一个列位置
        int firstCellNum = row.getFirstCellNum();
        //最后一列位置
        int lastCellNum = row.getLastCellNum();
        //空列数量
        int nullCellNum = 0;
        for (int c = firstCellNum; c < lastCellNum; c++) {
            Cell cell = row.getCell(c);
            if (null == cell || CellType.BLANK == cell.getCellTypeEnum()) {
                nullCellNum++;
            }
        }
        //所有列都为空
        if (nullCellNum == (lastCellNum - firstCellNum)) {
            return true;
        }
        return false;
    }

    /**
     * @Description: 给对象赋值
     * @Author: xiaolin
     * @Date: 2022/8/24
     */
    protected static <T> void setValue(Cell cell, Method method, Field field, T record) throws Exception {
        switch (cell.getCellTypeEnum()) {
            case BLANK:
                setProperties(method, field, record, "", cell);
                break;
            case STRING:
                // 字符串类型
                String stringValue = cell.getStringCellValue();
                setProperties(method, field, record, stringValue, cell);
                break;
            case BOOLEAN:
                // 布尔类型
                boolean booleanValue = cell.getBooleanCellValue();
                setProperties(method, field, record, booleanValue, cell);
                break;
            case FORMULA:
                // 公式类型与数字类型一起比较
            case NUMERIC:
                double value = cell.getNumericCellValue();
                // 公式类型与数字类型一起比较
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 日期类型的格式
                    Date date = DateUtil.getJavaDate(value);
                    setProperties(method, field, record, date, cell);
                } else {
                    // 数字类型的格式
                    if (value % 1 != 0) {
                        // 浮点型
                        setProperties(method, field, record, String.valueOf(value), cell);
                    } else {
                        // 整数型或（字符串被读取成整数型）
                        setProperties(method, field, record, new DecimalFormat("0").format(value), cell);
                    }
                }
                break;
            default:
                setProperties(method, field, record, cell.getStringCellValue(), cell);
                break;
        }
    }

    /**
     * @Description: 校验一级表头并生成属性与列的关联关系
     * @Author: xiaolin
     * @Date: 2022/8/10
     */
    public static <T> JSONObject checkPrimaryHeaderAndGetRelation(Sheet sheet, Class<T> clazz, Integer headerIndex) {
        // 属性名与中文名的关联关系
        Map<String, String> fieldRelations = getFieldNameAndValue(clazz);
        // 属性名与列索引的关联关系
        Map<String, Integer> fieldColRelations;
        JSONObject result = new JSONObject();
        fieldColRelations = new HashMap<>();
        // 获取表头行
        Row row = sheet.getRow(headerIndex);
        if (Objects.isNull(row)) {
            throw new ServiceException("解析不到表头信息，请检查表格文件！");
        }
        // 表头行最后一列
        int lastCellNum = row.getLastCellNum();
        // 所有列匹配的标志
        boolean allMatch = true;
        // 格式错误
        List<String> formatErrorList = new ArrayList<>();
        // 不匹配错误
        List<String> matchErrorList = new ArrayList<>();
        for (int i = 0; i <= lastCellNum; i++) {
            Cell cell = row.getCell(i);
            if (Objects.isNull(cell) || cell.getCellTypeEnum() == CellType.BLANK) {
                // 如果单元格不存在或者单元格为空单元格，直接返回之前收集的表头
                break;
            }
            if (cell.getCellTypeEnum() != CellType.STRING) {
                // 表头行都是字符串格式，如果不是则说明与表头行不匹配
                formatErrorList.add("第" + (i + 1) + "列");
                allMatch = false;
                continue;
            }
            String cellValue = cell.getStringCellValue();
            if (StringUtils.isBlank(cellValue)) {
                // 如果单元格为空串，直接返回之前收集的表头
                break;
            }
            // 获取表头信息
            String title = cellValue.trim();
            if (!fieldRelations.containsKey(title)) {
                // 表头不匹配
                matchErrorList.add(title);
                allMatch = false;
            } else {
                fieldColRelations.put(fieldRelations.get(title), i);
            }
        }
        if (!CollectionUtils.isEmpty(fieldColRelations) && allMatch) {
            result.put("isMatch", true);
            result.put("relations", fieldColRelations);
            return result;
        } else {
            result.put("isMatch", false);
            String errors = "";
            if (!CollectionUtils.isEmpty(formatErrorList)) {
                errors = "这些索引列 " + formatErrorList.toString() + " 格式错误";
            } else {
                errors = "这些表头列 " + matchErrorList.toString() + " 名称不匹配";
            }
            result.put("errorMsg", errors);
        }
        return result;
    }

    /**
     * @Description: 校验二级表头并生成属性与列的关联关系
     * @Author: xiaolin
     * @Date: 2022/8/10
     */
    public static <T> JSONObject checkSecondaryHeaderAndGetRelation(Sheet sheet, Class<T> clazz, Integer headerIndex) {
        // 属性名与中文名的关联关系
        Map<String, String> fieldRelations = getFieldNameAndValue(clazz);
        // 属性名与列索引的关联关系
        Map<String, Integer> fieldColRelations = new HashMap<>();
        JSONObject result = new JSONObject();
        // 获取二级表头的第一行
        Row row = sheet.getRow(headerIndex);
        // 获取二级表头的第二行
        Row nextRow = sheet.getRow(headerIndex + 1);
        if (Objects.isNull(row) || Objects.isNull(nextRow)) {
            throw new ServiceException("解析不到表头信息，请检查表格文件！");
        }
        // 获取表头最后一列
        short lastCellNum = row.getLastCellNum();
        // 所有列匹配的标志
        boolean allMatch = true;
        List<String> matchErrorList = new ArrayList<>();
        int tempIndex = 0;
        // 不匹配错误
        while (tempIndex <= lastCellNum) {
            // 第一行单元格
            Cell cell = row.getCell(tempIndex);
            // 第二行单元格
            Cell nextCell = nextRow.getCell(tempIndex);
            if (Objects.isNull(cell)) {
                // 如果单元格不存在，直接返回之前收集的表头
                break;
            }
            if (cell.getCellTypeEnum() == CellType.BLANK && nextCell.getCellTypeEnum() == CellType.BLANK) {
                // 如果单元格为空单元格，直接返回之前收集的表头
                break;
            }
            String cellValue = cell.getStringCellValue();

            String nextCellValue = null;
            if (!Objects.isNull(nextCell)) {
                nextCellValue = nextCell.getStringCellValue();
            }

            if (StringUtils.isBlank(cellValue) && StringUtils.isBlank(nextCellValue)) {
                // 如果单元格为空串，直接返回之前收集的表头
                break;
            }
            if (StringUtils.isNotEmpty(cellValue) && StringUtils.isEmpty(nextCellValue)) {
                // 第一种情况，合并上下单元格的情况
                String title = cellValue.trim();
                if (!fieldRelations.containsKey(title)) {
                    matchErrorList.add(title);
                    allMatch = false;
                } else {
                    fieldColRelations.put(fieldRelations.get(title), tempIndex);
                }
                tempIndex++;
            } else if (StringUtils.isNotEmpty(cellValue) && StringUtils.isNotEmpty(nextCellValue)) {
                // 第二种情况，多列单元格合并的情况
                String title = cellValue.trim() + "-" + nextCellValue.trim();
                if (!fieldRelations.containsKey(title)) {
                    matchErrorList.add(title);
                    allMatch = false;
                } else {
                    fieldColRelations.put(fieldRelations.get(title), tempIndex);
                }
                tempIndex++;
                boolean match = true;
                while (Objects.nonNull(row.getCell(tempIndex)) &&
                        StringUtils.isEmpty(row.getCell(tempIndex).getStringCellValue())) {
                    // 循环遍历同一组一级表头的二级表头列
                    nextCell = nextRow.getCell(tempIndex);
                    nextCellValue = nextCell.getStringCellValue();
                    // 匹配格式
                    title = cellValue.trim() + "-" + nextCellValue.trim();
                    if (!fieldRelations.containsKey(title)) {
                        matchErrorList.add(title);
                        match = false;
                    } else {
                        fieldColRelations.put(fieldRelations.get(title), tempIndex);
                    }
                    tempIndex++;
                }
                if (!match) {
                    allMatch = false;
                }
            }
        }
        if (!CollectionUtils.isEmpty(fieldColRelations) && allMatch) {
            result.put("isMatch", true);
            result.put("relations", fieldColRelations);
            return result;
        } else {
            result.put("isMatch", false);
            String errors = "这些表头列 " + matchErrorList.toString() + " 名称不匹配";
            result.put("errorMsg", errors);
        }
        return result;
    }


    public static <T> void setProperties(Method method, Field field, T record, Object value, Cell cell) throws InvocationTargetException, IllegalAccessException, ParseException {
        Class<?> fieldType = field.getType();
        if (Boolean.TYPE == fieldType || Boolean.class == fieldType) {
            // 布尔值
            if (value instanceof Boolean) {
                method.invoke(record, value);
            }
            if (value instanceof String && StringUtils.isNotBlank(String.valueOf(value))) {
                // 布尔值有可能读取成字符串(前后有空格造成)，需将字符串转成布尔值，去掉半角空格和全角空格
                value = Boolean.valueOf(((String) value).trim().replaceAll(" ", ""));
                method.invoke(record, value);
            }
        } else if (String.class == fieldType) {
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (value instanceof Date) {
                // 日期转成字符串格式
                if (StringUtils.isNotEmpty(excelField.format())) {
                    method.invoke(record, new SimpleDateFormat(excelField.format()).format((Date) value));
                } else {
                    method.invoke(record, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) value));
                    log.info("未指明日期格式化格式,直接转成年月日时分秒格式");
                }
            } else {
                //校验日期格式（“yyyy-MM”）
                if (StringUtils.isNotEmpty(excelField.format())) {
                    Date date = new Date((String) value);
                    ServiceException.throwEx(Objects.isNull(date), "第" + (cell.getRowIndex() + 1) + "行日期格式有误（如2000-01）");

                    String format = new SimpleDateFormat(excelField.format()).format(date);
                    method.invoke(record, format);
                } else {
                    // 字符串
                    method.invoke(record, ((String) value).trim());
                }
            }
        } else if (Date.class == fieldType) {
            if (value instanceof Date) {
                // 日期格式
                method.invoke(record, value);
            } else if (value instanceof String) {
                if (StringUtils.isNotBlank((String) value)) {
                    //校验日期格式
                    Date date = new Date((String) value);
                    ServiceException.throwEx(Objects.isNull(date), "第" + (cell.getRowIndex() + 1) + "行日期格式有误（如2000-01）");

                    ExcelField excelField = field.getAnnotation(ExcelField.class);
                    if (StringUtils.isNotEmpty(excelField.format())) {
                        // 日期读取成了字符串格式
                        method.invoke(record, new SimpleDateFormat(excelField.format()).parse((String) value));
                    } else {
                        method.invoke(record, new SimpleDateFormat("yyyy-MM").parse((String) value));
                        log.info("未指明日期格式化格式,直接转成年月格式");
                    }
                }
            }
        } else if (BigDecimal.class == fieldType) {
            String decimal = String.valueOf(value);
            if (StringUtils.isNotBlank(decimal)) {
                // 可能读取成字符串(前后有空格造成)，去掉半角空格和全角空格
                decimal = decimal.trim().replaceAll(" ", "");

                // 是否是数字
                boolean isNumber = NumberUtils.isNumber(decimal);
                ServiceException.throwEx(!isNumber, "第" + (cell.getRowIndex() + 1) + "行数字格式有误");

                method.invoke(record, new BigDecimal(decimal));
            } else {
                method.invoke(record, new BigDecimal("0"));
            }
        } else if (Double.class == fieldType) {
            String decimal = String.valueOf(value);
            if (StringUtils.isNotBlank(decimal)) {
                // 可能读取成字符串(前后有空格造成)，去掉半角空格和全角空格
                decimal = decimal.trim().replaceAll(" ", "");

                // 是否是数字
                boolean isNumber = NumberUtils.isNumber(decimal);
                ServiceException.throwEx(!isNumber, "第" + (cell.getRowIndex() + 1) + "行数字格式有误");

                method.invoke(record, new BigDecimal(decimal).doubleValue());
            } else {
                method.invoke(record, new BigDecimal("0").doubleValue());
            }
        } else if (Float.class == fieldType) {
            String decimal = String.valueOf(value);
            if (StringUtils.isNotBlank(decimal)) {
                // 可能读取成字符串(前后有空格造成)，去掉半角空格和全角空格
                decimal = decimal.trim().replaceAll(" ", "");

                // 是否是数字
                boolean isNumber = NumberUtils.isNumber(decimal);
                ServiceException.throwEx(!isNumber, "第" + (cell.getRowIndex() + 1) + "行数字格式有误");

                method.invoke(record, new BigDecimal(decimal).floatValue());
            } else {
                method.invoke(record, new BigDecimal("0").floatValue());
            }
        } else if (Long.class == fieldType) {
            String decimal = String.valueOf(value);
            if (StringUtils.isNotBlank(decimal)) {
                // 可能读取成字符串(前后有空格造成)，去掉半角空格和全角空格
                decimal = decimal.trim().replaceAll(" ", "");

                // 是否是数字
                boolean isNumber = NumberUtils.isNumber(decimal);
                ServiceException.throwEx(!isNumber, "第" + (cell.getRowIndex() + 1) + "行数字格式有误");

                method.invoke(record, new BigDecimal(decimal).longValue());
            } else {
                method.invoke(record, new BigDecimal("0").longValue());
            }
        } else if (Integer.class == fieldType) {
            String decimal = String.valueOf(value);
            if (StringUtils.isNotBlank(decimal)) {
                // 可能读取成字符串(前后有空格造成)，去掉半角空格和全角空格
                decimal = decimal.trim().replaceAll(" ", "");

                // 是否是数字
                boolean isNumber = NumberUtils.isNumber(decimal);
                ServiceException.throwEx(!isNumber, "第" + (cell.getRowIndex() + 1) + "行数字格式有误");

                method.invoke(record, new BigDecimal(decimal).intValue());
            } else {
                method.invoke(record, new BigDecimal("0").intValue());
            }
        }
    }

    protected static <T> void setNullProperties(Method method, Field field, T record) throws InvocationTargetException, IllegalAccessException, ParseException {
        Class<?> fieldType = field.getType();
        if (String.class == fieldType) {
            method.invoke(record, "");
        } else if (BigDecimal.class == fieldType) {
            method.invoke(record, new BigDecimal("0"));
        } else if (Double.class == fieldType) {
            method.invoke(record, 0.0);
        } else if (Float.class == fieldType) {
            method.invoke(record, 0.0F);
        } else if (Long.class == fieldType) {
            method.invoke(record, 0L);
        } else if (Integer.class == fieldType) {
            method.invoke(record, 0);
        }
    }

    /**
     * @Description: 收集属性名和中文名
     * @Author: xiaolin
     * @Date: 2022/6/23
     */
    public static Map<String, String> getFieldNameAndValue(Class clazz) {
        // 获取类中所有的属性
        Field[] fields = clazz.getDeclaredFields();
        // 存放收集的属性
        Map<String, String> relations = new HashMap<>();
        for (int i = 0; i < fields.length; i++) {
            // 解析注解
            Field field = fields[i];
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            if (Objects.nonNull(excelField)) {
                // 排除非excel表的字段
                relations.put(excelField.value(), field.getName());
            }
        }
        return relations;
    }

    /**
     * @Description: 收集属性对应的列号和中文名
     * @Author: xiaolin
     * @Date: 2022/6/23
     */
    public static String[][] getFieldMarks(Class clazz) {
        // 获取类中所有的属性
        Field[] fields = clazz.getDeclaredFields();
        // 存放收集的属性
        String[][] marks = new String[fields.length][];
        for (int i = 0; i < fields.length; i++) {
            // 解析注解
            ExcelField excelField = fields[i].getAnnotation(ExcelField.class);
            String[] mark = new String[2];
            mark[0] = Integer.toString(excelField.colNum());
            mark[1] = excelField.value();
            marks[i] = mark;
        }
        return marks;
    }

    /**
     * @Description:将Excel的日期转成字符串格式
     * @Author: xiaolin
     * @Date: 2022/6/22
     */
    private static String convertExcelDateToString(Date date, Cell cell) {
        String dateStr = "";
        if (date.getTime() % 86400000 == 16 * 3600 * 1000 && cell.getCellStyle().getDataFormat() == 14) {
            dateStr = new SimpleDateFormat("yyyy-MM-dd").format(date);
        } else {
            dateStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
        }
        return dateStr;
    }

    /**
     * @Description:将Excel的数字转成字符串格式
     * @Author: xiaolin
     * @Date: 2022/6/22
     */
    private static String convertExcelNumberToString(String numberFormat, Cell cell) {
        if (numberFormat.contains("%")) {
            // 百分比直接返回
            return numberFormat;
        }
        String numberStr = "";
        // 货币格式，如：1,200.00
        if (numberFormat.contains(",")) {
            numberFormat = numberFormat.replace(",", "");
        }
        // 科学计算法
        if (numberFormat.contains("E")) {
            //4.89481368464913E14还原为长整数
            numberFormat = new DecimalFormat("0").format(cell.getNumericCellValue());
            numberStr = Long.toString(Long.parseLong(numberFormat));
        } else {
            if (numberFormat.contains(".")) {
                // 小数
                numberStr = Double.toString(Double.parseDouble(numberFormat));
            } else {
                // 转换为整数
                numberStr = Long.toString(Long.parseLong(numberFormat));
            }
        }
        return numberStr;
    }

    /**
     * @Description: 获取表头索引
     * @Author: xiaolin
     * @Date: 2022/6/23
     */
    public static int getHeaderIndex(Sheet sheet, String[][] fieldMarks) {
        Iterator<Row> rowIterator = sheet.iterator();
        // 表头索引
        int index = 0;
        while (rowIterator.hasNext()) {
            Row header = rowIterator.next();
            int count = 0;
            for (String[] fieldMark : fieldMarks) {
                Cell cell = header.getCell(Integer.parseInt(fieldMark[0]));
                if (cell == null) {
                    // 单元格不存在，说明与表头行不匹配
                    break;
                } else {
                    if (cell.getCellTypeEnum() != CellType.STRING) {
                        // 表头行都是字符串格式，如果不是则说明与表头行不匹配
                        break;
                    } else {
                        String cellValue = cell.getStringCellValue();
                        if (StringUtils.isNotEmpty(cellValue) && fieldMark[1].equals(cellValue.trim())) {
                            // 找到符合格式的表头单元格
                            count++;
                        }
                    }
                }
            }
            if (count == fieldMarks.length) {
                // 匹配到了表头行，返回表头行的行索引
                return index;
            }
            index++;
        }
        return -1;
    }

    /**
     * @Description: 建立表头关联关系
     * @Author: xiaolin
     * @Date: 2022/6/22
     */
    private static JSONObject getHeaderObj(Sheet sheet, Map<String, String> params) {
        JSONObject jsonObj = new JSONObject();
        Iterator<Row> rowIterator = sheet.iterator();
        // 表头索引
        int index = 0;
        while (rowIterator.hasNext()) {
            Row header = rowIterator.next();
            // 单元格匹配集合
            List<String> titles = new ArrayList<>();
            for (int i = 0; i < params.size(); i++) {
                Cell cell = header.getCell(i);
                if (cell == null) {
                    // 单元格不存在，说明与表头行不匹配
                    break;
                } else {
                    if (cell.getCellTypeEnum() != CellType.STRING) {
                        // 表头行都是字符串类型，遇到非字符类型的单元格，直接跳过一整行
                        break;
                    } else {
                        String cellValue = cell.getStringCellValue();
                        if (StringUtils.isNotEmpty(cellValue) && params.containsKey(cellValue.trim())) {
                            titles.add(params.get(cellValue.trim()));
                        }
                    }
                }
            }
            if (titles.size() == params.size()) {
                // 表头与传入的格式相符，则说明找到了表头行
                jsonObj.put("index", index);
                jsonObj.put("titles", titles);
                return jsonObj;
            }
            index++;
        }
        // 不匹配则最后返回-1
        jsonObj.put("index", -1);
        jsonObj.put("titles", null);
        return jsonObj;
    }

    /**
     * @Description: 获取Iterator<Sheet>
     * @Author: xiaolin
     * @Date: 2022/6/21
     */
    public static Iterator<Sheet> getSheetIterator(String filePath) throws IOException {
        //获取文件输入流
        InputStream inputStream = getExcelInputStream(filePath);
        // 获取文件扩展名
        String extName = getFileExtName(filePath);
        // 获取 Iterator<Sheet> 对象
        Iterator<Sheet> sheetIterator = null;
        if ("xls".equals(extName)) {
            // 处理.xls后缀的文件
            HSSFWorkbook excel = new HSSFWorkbook(inputStream);
            sheetIterator = excel.sheetIterator();
        } else if ("xlsx".equals(extName)) {
            // 处理.xlsx后缀的文件
            XSSFWorkbook excel = new XSSFWorkbook(inputStream);
            sheetIterator = excel.sheetIterator();
        } else {
            log.error("请上传表格文件,错误的文件类型：{}", extName);
            throw new ServiceException("请上传表格文件");
        }
        return sheetIterator;
    }

    /**
     * @Description: 获取文件扩展名
     * @Author: xiaolin
     * @Date: 2022/6/22
     */
    protected static String getFileExtName(String filePath) {
        return filePath.substring(filePath.lastIndexOf('.') + 1);
    }

    /**
     * @Description: 获取文件输入流
     * @Author: xiaolin
     * @Date: 2022/6/10
     */
    public static InputStream getExcelInputStream(String filePath) throws IOException {
        if (filePath.startsWith("http://") || filePath.startsWith("https://")) {
            URL url = new URL(filePath);
            URLConnection urlConnection = url.openConnection();
            return urlConnection.getInputStream();
        } else {
            return new FileInputStream(new File(filePath));
        }
    }
}

